<?php defined('SYSPATH') OR die('No direct access allowed.');

class WB_Db {
	
	private $form;
	private $db;
	private $show_columns = null;
	
	private $table;
	private $primary_key = 'id';
	
	private $orderby = 'id';
	private $limit = 20;
	private $where = '1';
	private $restriction = '1';		//restriction is the same as where but always applies
	
	private $addable = FALSE;
	private $editable = FALSE;	
	private $deletable = FALSE;
	private $trashable = FALSE;
	private $in_trash = FALSE;
	private $trash_col = 'trashed';
	private $del_col = FALSE;
	
	private $last_row = array();
	private $last_id = 0;
	
	
	/**
	 * Constructor for WB_Db object
	 * 
	 * @param $form WB_Form object
	 * @return void
	 */
	public function __construct($form=null, $table=null)
	{
		$this->form = $form;
		$this->table = $table;
		$this->db = Kohana::instance()->db;
	}
	
	/**
	 * Sets the default database table options
	 * 
	 * @param array $init
	 * @return void
	 */
	public function initialize($init)
	{
		$this->table = isset($init['table']) ? $init['table'] : $this->table;
		$this->form = isset($init['form']) ? $init['form'] : $this->form;
		
		$this->primary_key = isset($init['primary_key']) ? $init['primary_key'] : $this->primary_key;
		$this->show_columns = isset($init['show_columns']) ? $init['show_columns'] : $this->show_columns;
		if (is_array($this->show_columns) && ! in_array($this->primary_key, $this->show_columns)) array_unshift($this->show_columns, $this->primary_key);
		
		$this->where = isset($init['where']) ? $init['where'] : $this->where;
		$this->limit = isset($init['limit']) ? $init['limit'] : $this->limit;
		$this->orderby = $this->table.'.'.$this->primary_key;
		$this->orderby = isset($init['orderby']) ? $init['orderby'] : $this->orderby;
		
		$this->addable = isset($init['addable']) ? $init['addable'] : $this->addable;
		$this->editable = isset($init['editable']) ? $init['editable'] : $this->editable;
		$this->deletable = isset($init['deletable']) ? $init['deletable'] : $this->deletable;
		$this->del_col = isset($init['del_col']) ? $init['del_col'] : $this->del_col;
		$this->trashable = isset($init['trashable']) ? $init['trashable'] : $this->trashable;
		$this->trash_col = isset($init['trash_col']) ? $init['trash_col'] : $this->trash_col;
	}
	
	public function get_addable()
	{
		return $this->addable;
	}
	
	public function get_editable()
	{
		return $this->editable;
	}
	
	public function get_trashable()
	{
		return $this->trashable;
	}
	
	public function get_deletable()
	{
		return $this->deletable;
	}
	
	public function get_in_trash()
	{
		return $this->in_trash;
	}
	
	public function get_limit()
	{
		return $this->limit;
	}
	
	public function get_last_row($name=FALSE)
	{
		if ($name===FALSE) return $this->last_row;
		elseif (isset($this->last_row[$name])) return $this->last_row[$name];
		else return null;
	}
	
	public function get_last_id($name=FALSE)
	{
		return $this->last_id;
	}
	
	public function addto_last_row($key,$val)
	{
		$this->last_row[$key] = $val;
	}
	
	
	
	/**
	 * Sets if work with trash only
	 * 
	 * @param $in_trash
	 * @return void
	 */
	public function use_trash($in_trash=TRUE)
	{
		if ($this->trashable) $this->in_trash = $in_trash;
	}
	
	
	
	/**
	 * Sets the where part of the query
	 * 
	 * @param $where
	 * @return void
	 */
	public function set_where($where)
	{
		$this->where = empty($where) ? '1' : $where;
	}
	
	
	
	/**
	 * Sets the orderby part of the query
	 * 
	 * @param mixed $orderby
	 * @return string
	 */
	public function set_orderby($orderby)
	{
		if (is_string($orderby)) $this->orderby = $orderby;
		elseif (is_array($orderby))
		{
			$db_tablenames = $this->form->collect_data('get_table_tablename', array_keys($orderby));
			$this->orderby = '';
			foreach ($orderby as $name=>$dir)
			{
				if (isset($db_tablenames[$name])) $this->orderby .= $db_tablenames[$name].' '.$dir.', ';
			}
			if ( ! $this->orderby) $this->orderby = $this->table.'.'.$this->primary_key;
			else $this->orderby = substr($this->orderby, 0, -2);
		}
		return $this->orderby;
	}
	
	
	
	/**
	 * Sets the table restriction
	 * It is stored as an sql where part and applied to all database actions
	 * 
	 * @param $restriction
	 * @return void
	 */
	public function set_restriction($restriction)
	{
		$this->restriction = empty($restriction) ? '1' : $restriction;
	}
	
	public function get_restriction()
	{
		$restriction = '(';
		if (strlen($this->restriction)>1) $restriction .= $this->restriction.' AND ';
		if ($this->trashable) $restriction .= ($this->in_trash ? $this->table.'.'.$this->trash_col.' != 0' : $this->table.'.'.$this->trash_col.' = 0').' AND ';
		if ($this->deletable && $this->del_col) $restriction .= $this->table.'.'.$this->del_col.' = 0 AND ';
		$restriction = strlen($restriction)>5 ? substr($restriction, 0, -5).')' : '1';
		return $restriction;
	}
	
	
	
	/**
	 * Get table data from database and runs each row through each element
	 * 
	 * @param int $page
	 * @param int $limit
	 * @return array
	 */
	public function populate_table($page=1, $limit=0)
	{
		$limit = empty($limit) ? $this->limit : $limit;
		$page = $page>1 && $page==floor($page) ? $page : 1;
		$offset = ($page-1) * $limit;
		
		$needed_items = $this->form->collect_data('table_columns', $this->show_columns);
		$select = $this->get_select($needed_items);
		$joins = $this->get_joins_arr($this->form->collect_data('table_joins', $this->show_columns));
		$where = $this->where.' AND '.$this->get_restriction();
		
		$this->db
			->select($select)
			->from($this->table)
			->where($where)
			->orderby($this->orderby)
			->limit($limit, $offset);
		
		foreach ($joins as $table_w_alias=>$join) 
		{
			$this->db->join($table_w_alias, $join[0], null, $join[1]);
		}
		
		$query = $this->db->get();
		
		$table = array();
		foreach ($query->result_array(FALSE) as $row)
		{
			$values = array();
			foreach ($needed_items as $name=>$needed_item)
			{
				foreach ($needed_item as $alias=>$cell) $values[$name][$alias] = $row[$alias];
				$values[$name] = count($values[$name])==1 ? reset($values[$name]) : $values[$name];
			}
			$this->form->set_all('set_value', $values, 'table');
			$table[] = $this->form->collect_data('render_cell', $this->show_columns);
		}
		//Kohana::log('info', $this->db->last_query());
		
		return $table;
	}
	
	
	
	/**
	 * Populates a table header with the currently shown element display name property
	 * or label property if no display name set
	 * or form name if no label set
	 * 
	 * @return array
	 */
	public function populate_head()
	{
		return $this->form->collect_data('get_display_name', $this->show_columns);
	}
	
	
	
	/**
	 * Finds the number of rows available in the last query
	 * 
	 * @return int
	 */
	public function populate_last_max()
	{
		$query = $this->db->query('SELECT COUNT(*) AS count FROM `'.$this->table.'` WHERE '.$this->where.' AND '.$this->get_restriction());
		
		//$where = $this->myescape($this->where.' AND '.$this->get_restriction(), $search);
		//$query = $this->db->where($where)
		
		return $query->current()->count;
	}
	
	
	
	/**
	 * Populates the form with database data
	 * 
	 * @param int $id
	 * @return boolean item loaded
	 */
	public function populate_form($id)
	{
		$this->where .= ' AND '.$this->primary_key.'='.$id;
		$needed_items = $this->form->collect_data('form_columns');
		$select = $this->get_select($needed_items);
		$joins = $this->get_joins_arr($this->form->collect_data('form_joins'));
		$where = $this->where.' AND '.$this->get_restriction();
		
		$this->db
					->select($select)
					->from($this->table)
					->where($where);
		
		foreach ($joins as $table_w_alias=>$join) $this->db->join($table_w_alias, $join[0], null, $join[1]);
		
		$query = $this->db->get();
		if ($query->count())
		{
			foreach ($query->result_array(FALSE) as $row)
			{
				$values = array();
				foreach ($needed_items as $name=>$needed_item)
				{
					foreach ($needed_item as $alias=>$cell) $values[$name][$alias] = $row[$alias];
					$values[$name] = count($values[$name])==1 ? reset($values[$name]) : $values[$name];
				} 
				$this->form->set_all('set_value', $values, 'form');
			}
			return TRUE;
		}
		
		return FALSE;
	}
	
	
	
	/**
	 * Edits or adds a new item in/to the database
	 * 
	 * @param array $post usally posted data
	 * @param int $id
	 * @return int affected rows
	 */
	public function set_item($post, $id=0)
	{
		if ($id && ! $this->editable) throw new Kohana_Exception('admin.editing_not_allowed');
		elseif ( ! $id && ! $this->addable) throw new Kohana_Exception('admin.adding_not_allowed');
		
					//echo '<h1>POST</h1><pre>'.print_r($post, TRUE).'</pre>';
		
		$needed_items = $this->form->collect_data('db_columns');
					//echo '<h1>NEEDED_ITEMS</h1><pre>'.print_r($needed_items, TRUE).'</pre>';
		$post[$this->primary_key] = $id;
		$values = array();
		foreach ($needed_items as $name=>$needed_item)
		{
			foreach ($needed_item as $alias=>$cell) $values[$name][$alias] = isset($post[$alias]) ? $post[$alias] : NULL;
			$values[$name] = count($values[$name])==1 ? reset($values[$name]) : $values[$name];
		}
		
		$this->form->set_all('set_value', $values, $id ? 'edit' : 'add');
		
		$errors = $this->form->collect_data('error_msg');
		if ( ! empty($errors))
		{
			$um = Kohana::instance()->user_messages;
			foreach ($errors as $msg) $um->add($msg, 'error');
			return FALSE;
		}
		
		$db_data = $this->form->collect_data('prep_db');
		
					//echo '<h1>DB_DATA</h1><pre>'.print_r($db_data, TRUE).'</pre>';
		$db_data = $this->flattened_array($db_data);
		
		$set = array();
		foreach ($db_data as $k=>$v) {
			$key = isset($needed_items[$k]) && isset($needed_items[$k][$k]) ? $needed_items[$k][$k] : $k;
			$set[$key] = $v;
		}
					//echo '<h1>SET</h1><pre>'.print_r($set, TRUE).'</pre>';
					//die();
		if (empty($set)) return 0;
		
		if ($id)
		{
			$query = $this->db
						->set($set)
						->where($this->table.'.'.$this->primary_key.'='.$id.' AND '.$this->get_restriction())
						->update($this->table);
			//$query = $this->db->query('UPDATE '.$this->table.' SET '.$set.' WHERE `'.$this->table.'`.`'.$this->primary_key.'`='.$id.' AND '.$this->get_restriction());
			$this->last_id = $id;
			$this->last_row[$this->primary_key] = $id;
			Event::run('wb_db.post_edit_item');
		}
		else
		{
			$query = $this->db
						->set($set)
						->insert($this->table);
			//$query = $this->db->query('INSERT INTO '.$this->table.' SET '.$set);
			$this->last_id = $query->insert_id();
			$this->last_row[$this->primary_key] = $this->last_id;
			Event::run('wb_db.post_add_item');
		}
		//Kohana::log('info', $this->db->last_query());
		return $query->count();
	}
	
	
	
	/**
	 * Trashes an item in database
	 * 
	 * @param int $id
	 * @return int affected_rows
	 */
	public function trash_item($id)
	{
		if ( ! $this->trashable) throw new Kohana_Exception('admin.trashing_not_allowed');
		
		$query = $this->db
						->set($this->table.'.'.$this->trash_col, mktime())
						->where($this->table.'.'.$this->primary_key.'='.$id.' AND '.$this->get_restriction())
						->update($this->table);
		//$query = $this->db->query('UPDATE `'.$this->table.'` SET `'.$this->table.'`.`'.$this->trash_col.'`='.mktime().' WHERE `'.$this->table.'`.`'.$this->primary_key.'`='.$id.' AND '.$this->get_restriction());
		
		return $query->count();
		
	}
	
	
	
	/**
	 * Untrashes an item in database
	 * 
	 * @param int $id
	 * @return int affected_rows
	 */
	public function untrash_item($id)
	{
		if ( ! $this->trashable) throw new Kohana_Exception('admin.trashing_not_allowed');
		
		$query = $this->db
						->set($this->table.'.'.$this->trash_col, 0)
						->where($this->table.'.'.$this->primary_key.'='.$id.' AND '.$this->get_restriction())
						->update($this->table);
		//$query = $this->db->query('UPDATE `'.$this->table.'` SET `'.$this->table.'`.`'.$this->trash_col.'`=0 WHERE `'.$this->table.'`.`'.$this->primary_key.'`='.$id.' AND '.$this->get_restriction());
		return $query->count();
	}
	
	
	
	/**
	 * Deletes an item from database
	 * 
	 * @param int $id
	 * @return int affected_rows
	 */
	public function delete_item($id)
	{
		if ( ! $this->deletable) throw new Kohana_Exception('admin.deleting_not_allowed');
		
		if ($this->del_col) 
		{
		
			$query = $this->db
						->set($this->table.'.'.$this->del_col, 0)
						->where($this->table.'.'.$this->primary_key.'='.$id.' AND '.$this->get_restriction())
						->update($this->table);
			//$query = $this->db->query('UPDATE `'.$this->table.'` SET `'.$this->table.'`.`'.$this->del_col.'`=0 WHERE `'.$this->table.'`.`'.$this->primary_key.'`='.$id.' AND '.$this->get_restriction());
		}
		else
		{
			$where = $this->table.'.'.$this->primary_key.'='.$id.' AND '.$this->get_restriction();
			$query = $this->db->where($where)->get($this->table);
			if ($query->count()<1) return 0;
			else 
			{
				$result_arr = $query->result_array(FALSE);
				$this->last_row = $result_arr[0];
			}
			
			$query = $this->db
						->where($where)
						->delete($this->table);
			//$query = $this->db->query('DELETE FROM `'.$this->table.'` WHERE `'.$this->table.'`.`'.$this->primary_key.'`='.$id.' AND '.$this->get_restriction());
			Event::run('wb_db.post_delete_item');
		}
		//Kohana::log('info', $this->db->last_query());
		
		return $query->count();
	}
	
	
	
	public function trash_all($ids)
	{
		$count = 0;
		foreach ($ids as $id) $count += $this->trash_item($id);
		return $count;
	}
	public function untrash_all($ids)
	{
		$count = 0;
		foreach ($ids as $id) $count += $this->untrash_item($id);
		return $count;
	}
	public function delete_all($ids)
	{
		$count = 0;
		foreach ($ids as $id) $count += $this->delete_item($id);
		return $count;
	}
	
	/**
	 * Helper function to get the select part of a query
	 * @param $items
	 * @return string
	 */
	private function get_select($items)
	{
		$unique = array();
		foreach($items as $item)
		{
			foreach ($item as $alias=>$col) $unique[$alias] = $col.' AS '.$alias;
		} 
		return implode(', ', $unique);
	}
	
	/**
	 * Helper function to get the join part of a query
	 * 
	 * items example: 
	 * array(	
	 * 		'counties' => array('counties', 'cities.county_id=counties.id'),
	 * 		'mayors' => array('people', 'cities.mayor_id=people.id', 'JOIN'),
	 * )
	 * 
	 * @param $items
	 * @return string
	 */
	private function get_joins_arr($items)
	{
		$unique = array();
		foreach($items as $item)
		{
			foreach ($item as $k=>$join) 
			{
				if (empty($join)) continue;
				$type = isset($join[2]) ? strtoupper($join[2]) : 'LEFT';
				$table_w_alias = is_numeric($k) ? $join[0] : $join[0].' AS '.$k;
				$on_arr = explode('AND',$join[1]);
				$on = array();
				foreach ($on_arr as $v) 
				{
					$tmp =  explode('=', $v);
					$on[trim($tmp[0])] = trim($tmp[1]);
				}
				$unique[$table_w_alias] = array($on, $type);
			}
		}
		return $unique;
	}
	
	private function myescape($sql, $arr)
	{
		foreach ($arr as $str) if ($str) $replace[$str] = '`'.$str.'`';
		return str_replace(array_keys($replace), $replace, $sql);
	}
	
	private function flattened_values($array)
	{
		$found = array();
		foreach ($array as $level1) 
			if (is_array($level1)) foreach ($level1 as $level2) 
				if (is_array($level2)) foreach ($level2 as $level3) $found[] = $level3;
				else $found[] = $level2;
			else $found[] = $level1;
		return array_keys(array_flip($found));
	}
	
	private function flattened_array($array)
	{
		$found = array();
		foreach ($array as $k1=>$level1) 
			if (is_array($level1)) foreach ($level1 as $k2=>$level2) 
				if (is_array($level2)) foreach ($level2 as $k3=>$level3) $found[$k3] = $level3;
				else $found[$k2] = $level2;
			else $found[$k1] = $level1;
			
		foreach ($found as $key=>$val) $found[$key] = $val=='NULL' ? NULL : $val;
		return $found;
	}
}

//End of WB_Db Library